<?php

namespace app\order\logic;

use think\facade\Db;
use think\facade\Cache;
use util\Excel;
use think\exception\ValidateException;
use think\facade\Log;
use util\Token;
use util\Upload;
use think\cache\driver\Redis;

class OrderReportRealTimeLogic
{

    public function __construct()
    {

    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $shop_id = $params['shop_id'] ?? 0;
        $spu = $params['spu'] ?? "";
        $payment_gateway_names = $params['payment_gateway_names'] ?? "";
        $country_code = $params['country_code'] ?? "";
        $shop_id = $params['shop_id'] ?? 0;

        $where = [];
        $where_campaign = [];
        //实时
        $yesterday = date('Y-m-d', time() - timezone());  
        $midnightYesterday = strtotime($yesterday . ' 00:00:00');
        $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
        $where[]  = ['a.createtime', '>=', $midnightYesterday];
        $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
        $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
        $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

        
        $where[]  = ['a.financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];
        if (!empty($shop_id)) {
            $where[] = ['a.shop_id', '=', $shop_id];
        }
        if (!empty($spu)) {
            $where[] = ['a.spu', 'like', '%' . trim($spu) . '%'];
        }
        $list = Db::name('order_achievement')
            ->field('
                COUNT(a.id) as quantity,
                sum(a.total_price) as total_price,
                a.shop_id,
                a.currency,
                b.name as shop_name,
                sum(a.skuquantity) as skuquantity,
                sum(a.freight) as freight,
                sum(a.material) as material
            ')
            ->alias('a')
            ->join('qu_shops b','b.id = a.shop_id', 'left')
            ->where($where)
            ->group('a.shop_id')
            ->paginate($limit);
        $lists = $list->items();
        
        //汇率-----使用最新的汇率
        $order_exchange = Db::name('order_exchange')->order('date desc')->find();

        //面板广告费
        $where_campaign[] = ['a.spend', '>=', 0.001];
        
        if(!empty($lists)){
            foreach ($lists as &$v) { 
                //预估物流--人民币转美金
                $v['freight'] = (float)number_format((float)$v['freight'] / $order_exchange['dollar'], 3, '.', '');
                //预估采购
                $v['material'] = (float)number_format((float)$v['material'] / $order_exchange['dollar'], 3, '.', '');
                //广告费
                $v['advertising'] = $this->advertising_campaign($where_campaign,$v['shop_id']);
                //退款
                $v['refund'] = (float)number_format((float)$v['total_price'] * 0.03, 3, '.', '');
                //ROI
                if ($v['advertising'] > 0.001 && $v['total_price'] > 0.001) {
                    $v['roi'] = number_format((float)$v['total_price'] / (float)$v['advertising'], 3, '.', '');
                }else{
                    $v['roi'] = 0;
                }
                //盈利
                $v['profit'] = number_format((float)$v['total_price'] - (float)$v['advertising'] - (float)$v['freight'] - (float)$v['material'] - (float)$v['refund'], 3, '.', '');
            }
        }
            
        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
    * 广告花费
    * @param array params
    */
    public function advertising_campaign($where_campaign,$shop_id)
    {
        $advertiser = Db::name('advertising')
            ->field('sum(a.spend) as spend')
            ->alias('a')
            ->join('qu_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->join('qu_advaccount_admin c', '
                c.advertiser_id = b.advertiser_id AND 
                c.createtime <= a.stat_time AND 
                c.updatetime > a.stat_time AND 
                c.shop_id = '.$shop_id.' AND 
                c.status = 0', 'left')
            ->join('qu_advaccount_admin c2', '
                c2.advertiser_id = b.advertiser_id AND 
                c2.createtime <= a.stat_time AND 
                c2.shop_id = '.$shop_id.' AND 
                c2.status = 1', 'left')
            ->where($where_campaign)
            ->where(function ($query) { 
                $query->where('COALESCE(c.shop_id, c2.shop_id) IS NOT NULL');
            })
            ->order("spend desc")
            ->find();
        $advertising_spend = 0;
        if (!empty($advertiser['spend'])) {
            $advertising_spend = number_format((float)$advertiser['spend'], 2, '.', '');
        }
        return $advertising_spend;
    }

    
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 交易国家列表
     * @author zhangheng
     */
    public function CountryList($params = [])
    {
        $data = [  
            ['value' => 'US', 'label' => 'United States'],  
            ['value' => 'CA', 'label' => 'Canada'],  
            ['value' => 'AU', 'label' => 'Australia'],  
            ['value' => 'MX', 'label' => 'Mexico'],  
            ['value' => 'GB', 'label' => 'United Kingdom'],  
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

}
